Server-side enforcement of complex, interrelated rules, part 2

By David Wendelken

David Wendelken is a senior consultant for Tactics, Inc. and uses examples like this for creating training materials. He is the co-author of The Oracle Designer/2000 Handbook. You can reach David by phone at (404) 248-1226 or via E-mail at 73523.2344@compuserve.com.

Last month, we set eight rather ambitious goals for our development project and accomplished the first four of them. If you missed the last issue or have misplaced your copy, check out our Web site at http://www. cobb.com/eod/ for last month’s article. Now it’s time to implement the next two goals. But first, a quick recap is in order (unless your memory is a lot better than mine!).

Background

A mythical company is building a fully integrated accounting system for resale. However, the sales department insists that the software must also be usable in a modular, mix-and-match manner. For example, some of our customers will want the entire system, while others will want just Payroll or just Job Cost.

Both Payroll and Job Cost need an em-ployee table; however, the business rules we want to enforce about employees will vary depending upon whether Payroll or Job Cost is installed. This is because Payroll requires more information about an employee than Job Cost does, and our customers who are using only Job Cost won’t want to be bound by the far stricter Payroll rules. For greater reliability of the delivered software, we want to enforce our rules with database server code. We’re concentrating on the employee table as a way to illustrate how to implement our rules in server-side code.

The code to implement this project currently consists of

• An employee table
• Separate views of the employee table for Job Cost and Payroll for Goal 1
• Before-insert-row and before-update-row database triggers on the employee table with a pair of database triggers to implement Goals 2, 3, and 4
• A company_control table to store the installation status of Payroll and Job Cost on a per-company basis
• A system_control package consisting of two functions that return whether Payroll or Job Cost are installed

Forward, ho!

We now need to implement the next two of our eight goals. Table A provides a brief description of these two goals.

Table A: These are the next two goals to implement.

Goal 5: Handle the de-installation of an application while others remain installed.

Table B shows, for all possible combinations of whether Payroll or Job Cost are installed, the update action that our server-side code should take for the annual_salary and citizenship columns in the employee table. For example, if Payroll is not installed and Job Cost is, users aren’t allowed to change the annual_salary column. In addition, the citizenship column should be set to a value of Null.

Table B: These are the column update validation rules for the employee table.

Actually, the before-update-row trigger on the employee table we created in the last issue took care of part of Goal 5. In fact, it admirably handles a user attempting to update the em-ployee record. However, it doesn’t automatically set the citizenship column to Null when payroll is de-installed, as Table B specifies.

To do that, we’ll need an after-update-row database trigger on the company_control table that will activate when Payroll is de-installed and Job Cost remains installed. The trigger must find each employee with a non-Null citizenship and set citizenship to Null. We’ll hold off on the coding until we take a good look at Goal 6.

Goal 6: Forbid Payroll transactions while pre-existing employee records are invalid.

As we mentioned in last month’s issue, it’s possible for a customer using only the Job Cost application to enter new employee records. Then, when Payroll is installed, the already existing employee records become partially invalid, because they aren’t in full conformance with the stricter Payroll rules. Because there will be a gap in time between the Payroll installation and correcting the existing employee records, we must forbid the creation of Payroll transactions (such as Payroll checks) until we have valid employee data. Because the insert and update triggers on the employee table prevent newly entered employee records from being invalid, we only need to worry about the pre-existing employees.

Logically, to implement this goal we’ll need several pieces of information before we decide to forbid Payroll transactions:

1. Has Payroll just been installed?
2. Are there any existing employee records?
3. If there are any employee records on file, are any of them now invalid based on the new Payroll rules?
4. If the answer to all three questions is yes, then we need to record that Payroll transactions are not allowed.

We’ll do this by setting company_ control.invalid_employees_ind to Y for the company that just installed Payroll. This won’t actually prevent Payroll transactions from taking place, but at least we’ll know that we should prevent them! Listing A shows three database triggers on the company_ control table that finish implementing Goal 5, as well as this portion of Goal 6.

Listing A: These triggers clear out unwanted Payroll data when Payroll is de-installed and record whether existing employee records are invalid when Payroll is installed.

CREATE OR REPLACE TRIGGER comc_bur 
  BEFORE UPDATE ON company_control FOR EACH ROW
DECLARE

-- Note that this code lacks production quality error handling.

ws_invalid_employees_ind company_control.invalid_employees_ind%TYPE;

BEGIN
-- Just to forestall problems...
  IF :OLD.company_id != :NEW.company_id THEN
      RAISE_APPLICATION_ERROR(-20001,'Not allowed to change the company id.');
  END IF;

  -- There are many possible combinations of events relating 		to the installation / 
  -- de-installation of Job Cost and Payroll.  We only need 		to handle two of them given
  -- the business rules in our example.
 
  -- Clear out unwanted employee payroll columns if payroll was just de-installed.
  IF  :NEW.payroll_installed_ind != :old.payroll_installed_ind 
  AND :NEW.payroll_installed_ind  = 'N' THEN
      -- Remember to update employees in the after statement 		   update trigger
      -- because a statement here, like this, will cause an 		   error:
      /* UPDATE employee
      || SET    citizenship = NULL
      || WHERE  company_id := :NEW.company_id;
      */
      system_control.set_payroll_deinstall_status(TRUE);
  END IF;

  -- Check for invalid employee data if we've just installed payroll.
  IF  (    :NEW.payroll_installed_ind != :OLD.payroll_installed_ind 
       AND :NEW.payroll_installed_ind  = 'Y' 
      )
  -- Verify that a change to the invalid_employee_ind value is a valid one.
  OR  (:NEW.invalid_employees_ind != :OLD.invalid_employees_ind) THEN
      ws_invalid_employees_ind := system_control.any_invalid_employees(:new.company_id);
      IF ws_invalid_employees_ind IN ('Y','N') THEN
         :NEW.invalid_employees_ind := ws_invalid_employees_ind;
      ELSE
         RAISE_APPLICATION_ERROR(-20001,'Unable to determine invalid employee status.');
      END IF;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM); 
END;
/
CREATE OR REPLACE TRIGGER comc_aus
AFTER UPDATE ON company_control 
BEGIN
  IF system_control.get_payroll_deinstall_status = TRUE THEN
     system_control.set_payroll_deinstall_status(FALSE);
     UPDATE employee empl
     SET    citizenship = NULL
     WHERE  citizenship IS NOT NULL
       AND  (company_id) = (SELECT comc.company_id FROM company_control comc
                             WHERE empl.company_id = comc.company_id
                               AND comc.payroll_installed_ind = 'N'
                           );
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM); 
END;
/
CREATE OR REPLACE TRIGGER comc_bus
BEFORE UPDATE ON company_control 
BEGIN
  system_control.set_payroll_deinstall_status(FALSE);
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM); 
END;
/

You probably expected a before-update trigger on company_control at the row level, but we also have a before-update statement and after-update statement trigger too! Why? The answer lies buried in the complex inter-action of our various code segments.

A cursory reading of the before-update row trigger on company_control in Listing A will show that we commented out a simple update statement on the employee table to set citizenship to Null when Payroll is de-installed. When we perform an update on employee, the employee table’s before-update row trigger queries company_control for the payroll installation status. Since we’re in the process of changing the company_control table, the employee trigger can’t get a valid answer, so it issues an error.

Therefore, as a design principle, we can’t have triggers that cause other triggers to fire and that require information in a table that is currently being changed. That’s a very rea-sonable limitation imposed by the server, but it’s very awkward for two reasons: We have to keep track of that sort of thing ourselves (or risk getting surprised at run time), and we need it to make those changes!

The answer is to delay the actual update of the employee table until the update of the company_control table is completed. Therefore, we use an after-update statement-level trigger on company_control to update the employee records. Unfortunately, statement-level triggers don’t have access to the row-level information, so they literally have no idea what rows were changed or how they were changed. We use our system_control package to pass a status variable from the row trigger to the statement trigger to let it know whether there is any work to do.

This is not the most efficient algorithm! However, installing and de-installing Payroll isn’t something the average company does very often, so this suits our immediate design needs. In a different context, we might want the system_control package to store the pri-mary key in a PL/SQL table (array) and have the after-statement trigger work from that list.

Of course, we need to extend the system_ control package with the functions mentioned in our last three triggers. Why did we use a package instead of placing the code in the triggers or in standalone functions and procedures? Packages let us store data in variables that are persistent through many statements, yet still provide the advantages of procedures and functions. It’s possible that we’ll want other programs to know if there are any invalid employee records still on file; thus we can reuse the code.

By the way, a user going into the company_ control maintenance screen and asserting that there are no more invalid employees (by chang-ing invalid_employees_ ind to N) can force the database to acknowledge that employee records are now valid (but only if they are right!). Listing B shows the additional code to add to the system control package and package body.

Listing B: This code determines if any existing employee records are invalid according to the Payroll rules.

-- Add to create system_control-- package statement

/* (Y)es if any employees are
|| invalid, (N)o if all ok, (E)rror */
FUNCTION any_invalid_employees (p_company_id IN NUMBER) RETURN CHAR;

/* TRUE or FALSE depending upon
|| whether the payroll de-install
|| logic needs to be run. 
*/
PROCEDURE 
   set_payroll_deinstall_status
        (p_new_status IN BOOLEAN);

/* TRUE or FALSE depending upon
|| whether payroll has just been de-|| installed. 
*/
FUNCTION get_payroll_deinstall_status RETURN BOOLEAN;

-- Add to create system_control
-- package body statement.
-- (Add more robust error handling.)

ws_payroll_deinstall_status 
          BOOLEAN := FALSE;

FUNCTION any_invalid_employees (p_company_id IN NUMBER) RETURN CHAR
IS

-- Citizenship must not be null if
-- payroll is installed.  Find if
-- violations exist.
   CURSOR find_invalid_employees IS
  SELECT 'Y' FROM dual
  WHERE EXISTS
   (SELECT 'X' 
      FROM employee
     WHERE citizenship IS NULL
       AND  company_id = p_company_id
   );

  ws_bad_employee_ind CHAR;
BEGIN
  OPEN  find_invalid_employees;
  FETCH find_invalid_employees 
   INTO ws_bad_employee_ind;
  IF find_invalid_employees%FOUND
  THEN
    RETURN 'Y';
  ELSE
    RETURN 'N';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'E';
END;

PROCEDURE
   set_payroll_deinstall_status
       (p_new_status IN BOOLEAN) IS
BEGIN
  ws_payroll_deinstall_status 
       := p_new_status;
END;

FUNCTION get_payroll_deinstall_status RETURN BOOLEAN IS
BEGIN
  RETURN ws_payroll_deinstall_status;
END;

Now it’s time to actually prevent Payroll transactions when employee records are invalid. Up to now, maximizing performance has taken a back seat to simplicity of expression, since we’re learning how to orchestrate many different server-side code objects to enforce our business rules and not concentrating upon PL/SQL code tuning. Since our users don’t install appli-cations very frequently, and most companies don’t insert or update employee records in huge numbers on a daily basis, this trade-off has been fairly safe. However, we can expect a high volume of accounting transactions in many companies, so we need to pay more attention to performance in this section of our code. That said, we’ll still start off with a simple implementation, then see whether we can improve the performance.

Every Payroll transaction table will need the same code in the before-row-insert, update, and delete database triggers. Quite simply, we’ll add a function to our system_ control package to tell us whether we can perform a payroll trans-action. Listing C shows the new function in system_control, and Listing D contains a sample Payroll transaction table database trigger.

Listing C: This function lets us know whether Payroll transactions are allowed.

-- Add to the create system_control package statement.

-- Returns (Y)es, (N)o, or (E)rror. 
FUNCTION are_payroll_transactions_ok (p_company_id IN NUMBER) RETURN CHAR;

-- Add to the create system_control package body statement.

FUNCTION are_payroll_transactions_ok (p_company_id IN NUMBER) RETURN CHAR
IS 
 ws_trans_ok company_control.invalid_employees_ind%TYPE := 'N';
BEGIN
  -- (N)o invalid employees means payroll transactions ok if Payroll installed.
  SELECT 'Y' INTO  ws_trans_ok
    FROM company_control
   WHERE company_id = p_company_id
     AND invalid_employees_ind = 'N'
     AND payroll_installed_ind = 'Y'; 

  RETURN ws_trans_ok;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'E';
END;

Listing D: Triggers like this prevent Payroll transactions when employee records are invalid.

/* 
|| One trigger per payroll transaction table.
|| Change trigger name and table name as needed. 
*/
CREATE OR REPLACE TRIGGER pr_trans_biudr
  BEFORE DELETE OR INSERT OR UPDATE 
  ON pr_trans FOR EACH ROW
BEGIN
  - This would be simpler if we
  - forbade changing company_id.
  IF (INSERTING AND system_control.are_payroll_transactions_ok(:NEW.company_id) != 'Y')
  OR (UPDATING  AND system_control.are_payroll_transactions_ok(:NEW.company_id) != 'Y')
  OR (DELETING  AND system_control.are_payroll_transactions_ok(:OLD.company_id) != 'Y')
  OR (    UPDATING 
      AND :NEW.company_id != :OLD.company_id
      AND system_control.are_payroll_transactions_ok(:OLD.company_id) != 'Y'
     )
  THEN
     RAISE_APPLICATION_ERROR(-20001,'Payroll transactions not allowed.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,SQLCODE || ':' || SQLERRM);
END;
/

Can we improve performance for high-volume transaction tables? Yes, but at a price. We could load our company_ control indicators into package variables and access them a bit faster. Or, we could opt for a complex set of PL/SQL routines using the dbms_pipe package if the log-off limitation was not acceptable, but we would benchmark the sample method before expending the labor on more complex methods.

Can we improve these code examples? Certainly! We need more robust error handling and more user-friendly error messages, as well as more efficient PL/SQL language elements. When training others using this case study, we also have them move the validation code out of the triggers into functions that are called by the triggers. Client-side programs can then use those functions for validating user data entry prior to commit time. We also have the students build a set of SQL test scripts to verify that the code will work as intended.

Next month, we’ll add security on a per-user per-company basis to our server-side code.

[The Cobb Group Home Page]

Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.